package com.ltv.xiangqibook.dbtool;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

import com.ltv.xiangqibook.common.GameIDCreator;
import com.ltv.xiangqibook.common.XiangQiBookConfiguration;
import com.ltv.xiangqibook.model.Game;

public class GameGenerator {
    private static final Logger logger = LogManager.getLogger(GameGenerator.class);

    static Connection con = null;
    static PreparedStatement insertGames = null;
    static PreparedStatement deleteGames = null;

    static String insertQuery = "insert into games(game_id, move_list, start_index, comment, game_url) " +
    		"values (?, ?, ?, ?, ?)";
    static String deleteQuery = "delete from games";

    public static void main(String[] args) {
        logger.info("Starting...");
        try {
            String xiangqiBookHome = args[0];
            XiangQiBookConfiguration.getInstance().loadConfiguration(xiangqiBookHome + "/" + XiangQiBookConfiguration.CONFIG_FILE_NAME);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            System.exit(1);
        }

        try {
            Class.forName("com.mysql.jdbc.Driver");
            String dbURL = "jdbc:mysql://" + XiangQiBookConfiguration.getInstance().getDBHost();
            dbURL += ":" + XiangQiBookConfiguration.getInstance().getDBPort();
            dbURL += "/" + XiangQiBookConfiguration.getInstance().getDBName();
            dbURL += "?useEncoding=true&characterEncoding=UTF-8";

            String dbUser = XiangQiBookConfiguration.getInstance().getDBUser();
            String dbPassword = XiangQiBookConfiguration.getInstance().getDBPassword();

            con =DriverManager.getConnection (dbURL, dbUser, dbPassword);
            con.setAutoCommit(false);
            insertGames = con.prepareStatement(insertQuery);
            deleteGames = con.prepareStatement(deleteQuery);

            deleteGames.execute();

            list(new File(XiangQiBookConfiguration.getInstance().getBookLocalLocation()));

            con.commit();
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            try {
                con.rollback();
            } catch (SQLException e1) {
                logger.error(e.getMessage(), e);
            }
        } finally {
            if (deleteGames != null) {
                try {
                    deleteGames.close();
                } catch (SQLException e) {
                    logger.error(e.getMessage(), e);
                }
            }
            if (insertGames != null) {
                try {
                    insertGames.close();
                } catch (SQLException e) {
                    logger.error(e.getMessage(), e);
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    logger.error(e.getMessage(), e);
                }
            }
        }
        
        logger.info("End.");

    }
    
    public static void list(File file) throws Exception {

        if (file.getName().indexOf(".svn") == -1) {
            File[] children = file.listFiles();

            if (children != null) {
                Arrays.sort(children);
                for (File child : children) {
                    if (child.getName().indexOf(".svn") == -1 && child.isFile() && child.getName().endsWith(".pgn")) {
                    	Game game = new Game();
                    	
                        String gameIdInMD5 = GameIDCreator.getGameID(child);
                        game.setGameId(gameIdInMD5);
                        game.setGameUrl(GameIDCreator.getGameURL(child));
                        
                        FileInputStream fis = null;
                        InputStreamReader isr = null;
                        BufferedReader in = null;
                        String inputLine;
                        
                        String moveList = "";
                        
                        try {
                            fis = new FileInputStream(child.getAbsolutePath());
                            isr = new InputStreamReader(fis);
                            in = new BufferedReader(
                                    new InputStreamReader(fis));
    
                            while ((inputLine = in.readLine()) != null) {
                                if (inputLine.trim().startsWith("[") || inputLine.trim().endsWith("]") || inputLine.trim().equals(""))
                                    continue;
                                moveList += inputLine.split("\\.")[1].trim().replace(' ', '+');
                                moveList += "+";
                            }
                        } finally {
                            if (fis != null) {
                                fis.close();
                            }
                            if (isr != null) {
                                isr.close();
                            }
                            if (in != null) {
                                in.close();
                            }
                        }
                        
                        if (!moveList.trim().equals("")) {
                            moveList = moveList.toUpperCase().substring(0, moveList.length()-1);
                        }
                        game.setMoveList(moveList);

                        // Get game comment
                        String comment = "";
                        Integer startIndex = 0;
                        StringBuffer startIndexStr = new StringBuffer();
                        
                        
                        boolean readstartIndex = false;
                        
                        try {
                            fis = new FileInputStream(child.getAbsolutePath().replace(".pgn", ".txt"));
                            isr = new InputStreamReader(fis, "utf-8");
                            in = new BufferedReader(isr);
    
                            while ((inputLine = in.readLine()) != null) {
                            	if (inputLine.equalsIgnoreCase("[start_index]")) {
                            		readstartIndex = true;
                            		continue;
                            	}
                            	
                            	if (inputLine.equalsIgnoreCase("[/start_index]")) {
                            		readstartIndex = false;
                            		continue;
                            	}
                            	
                            	if (readstartIndex) {
                            		startIndexStr.append(inputLine);
                            	} else {
                            		comment += inputLine + "\n";
                            	}
                            }
                            startIndex = Integer.parseInt(startIndexStr.toString());
                        } catch (Exception e) {
                            // Do nothing, text file not found, just let comment empty
                        } finally {
                            if (fis != null) {
                                fis.close();
                            }
                            if (isr != null) {
                                isr.close();
                            }
                            if (in != null) {
                                in.close();
                            }
                        }
                        
                        game.setComment(comment);
                        game.setStartIndex(startIndex);

                        insertGames.setString(1, game.getGameId());
                        insertGames.setString(2, game.getMoveList());
                        insertGames.setInt(3, game.getStartIndex());
                        insertGames.setString(4, game.getComment());
                        insertGames.setString(5, game.getGameUrl());
                        
                        insertGames.executeUpdate();
                    }
                    list(child);
                }
            }
        }
    }

}
